﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Jy123.BLL.Public;
using Jy123.Model;

namespace Jy123.BLL.User
{
    public class Search
    {
        /// <summary>
        /// 根据Email或姓名搜索好友
        /// </summary>
        /// <param name="key">Email或姓名</param>
        /// <param name="count">返回结果数量</param>
        /// <returns>数据集</returns>
        //public DataSet Search_Name_User(string key, int count)
        //{
        //    string sqlStr = "";
        //    sqlStr = sqlStr + "select TOP(@Count) a.ID,a.UserName,b.Favourite,b.BirthidayDisplay,b.Birthday, ";
        //        sqlStr = sqlStr + "(select Name from User_Dict_Area where id=a.Province) as Province, ";
        //        sqlStr = sqlStr + "(select Sex=case Sex when 0 then '美女' when 1 then '帅哥' end) as Sex, ";
        //        sqlStr = sqlStr + "(select Name from User_Dict_Area where id=a.City) as City, ";
        //        sqlStr = sqlStr + "a.Portrait ";
        //    sqlStr = sqlStr + "from User_Main a left join User_Info b on a.ID=b.ID  ";
        //    sqlStr = sqlStr + "where (a.Email like '%"+@key+"%' or a.UserName like '%"+@key+"%') and (a.State=5 or a.State=2) ";
        //    sqlStr = sqlStr + "order by a.ID desc ";
            
        //    SqlParameter[] parameters = {
        //                                new SqlParameter("@key", SqlDbType.NVarChar,50),
        //                                new SqlParameter("@count", SqlDbType.Int, 4)};
        //    parameters[0].Value = key;
        //    parameters[1].Value = count;
        //    return DBUtility.DbHelperSQL.Query(sqlStr, parameters);
        //}
        /// <summary>
        /// 根据Email或姓名搜索好友
        /// </summary>
        /// <param name="cpage">当前页</param>
        /// <param name="psize">需要获取的数量</param>
        /// <param name="key">筛选条件</param>
        /// <param name="counts">总数[0],页数[1]</param>
        /// <returns>数据集</returns>
        public DataSet Search_Name_User(int cpage, int psize, string key, out List<int?> counts)
        {
            return Pagination.GetList(new MPagination()
            {
                Filter = (string.IsNullOrEmpty(key)) ? null : "(Email like '%" + key + "%' or UserName like '%" + key + "%') and (State=3 or State=2)",
                CurrentPage = cpage,
                Fields = "*",
                PageSize = psize,
                PrimaryKey = "ID",
                Tables = "V_User_SearchNameOrUser"
            }, out counts);
        }
        /// <summary>
        /// 搜索好友
        /// </summary>
        /// <param name="key">Email或姓名</param>
        /// <param name="prov">省</param>
        /// <param name="city">市</param>
        /// <param name="oldStart">开始年龄</param>
        /// <param name="oldEnd">结束年龄</param>
        /// <param name="count">返回结果数量</param>
        /// <returns>数据集</returns>
        //public DataSet ToSearch(string key,string prov,string city,int oldStart,int oldEnd,int sex, int count)
        //{
        //    DateTime birthdayStart = DateTime.Today;
        //    DateTime birthdayEnd = DateTime.Today;
        //    if(oldStart!= 0){birthdayStart = DateTime.Parse((DateTime.Now.Year - oldStart).ToString());}
        //    if(oldEnd != 0) {birthdayEnd = DateTime.Parse((DateTime.Now.Year - oldEnd).ToString()); }

        //    string sqlStr = "";
        //    sqlStr = sqlStr + "select TOP(@Count) a.ID,a.UserName,b.Favourite,b.BirthidayDisplay,b.Birthday, ";
        //    sqlStr = sqlStr + "(select Name from User_Dict_Area where id=a.Province) as Province, ";
        //    sqlStr = sqlStr + "(select Sex=case Sex when 0 then '美女' when 1 then '帅哥' end) as Sex, ";
        //    sqlStr = sqlStr + "(select Name from User_Dict_Area where id=a.City) as City, ";
        //    sqlStr = sqlStr + "a.Portrait ";
        //    sqlStr = sqlStr + "from User_Main a left join User_Info b on a.ID=b.ID  ";

        //    sqlStr = sqlStr + "where (a.State=5 or a.State=2) ";
        //    if (key != null) { sqlStr = sqlStr + "and (a.Email like '@key' or a.UserName like '%" + @key + "%') "; }
        //    if (prov != null) { sqlStr = sqlStr + "and (a.Province like '%" + @prov + "%') "; }
        //    if (city != null) { sqlStr = sqlStr + "and (a.city like '%" + @city + "%') "; }
        //    if (birthdayStart != DateTime.Today) { sqlStr = sqlStr + "and (b.birthday>= '%" + @birthdayStart + "%') "; }
        //    if (birthdayEnd != DateTime.Today) { sqlStr = sqlStr + "and (b.birthday<= '%" + @birthdayEnd + "%') "; }
        //    //if (sex != null) { sqlStr = sqlStr + "and (b.sex= '" + @sex + "') "; }
        //    sqlStr = sqlStr + "order by a.ID desc ";
        //    SqlParameter[] parameters = {
        //            new SqlParameter("@key", SqlDbType.NVarChar,100),
        //            new SqlParameter("@prov", SqlDbType.Int,4),
        //            new SqlParameter("@city", SqlDbType.Int,4),
        //            new SqlParameter("@birthdayStart", SqlDbType.DateTime),
        //            new SqlParameter("@birthdayEnd", SqlDbType.DateTime),
        //            new SqlParameter("@sex", SqlDbType.Bit,1),
        //            new SqlParameter("@count", SqlDbType.Int, 4)};
        //    parameters[0].Value = key;
        //    parameters[1].Value = prov;
        //    parameters[2].Value = city;
        //    parameters[3].Value = birthdayStart;
        //    parameters[4].Value = birthdayEnd;
        //    parameters[5].Value = sex;
        //    parameters[6].Value = count;
        //    return DBUtility.DbHelperSQL.Query(sqlStr, parameters);
        //}
        

        /// <summary>
        /// 搜索好友
        /// </summary>
        /// <param name="cpage">当前页</param>
        /// <param name="psize">需要获取的数量</param>
        /// <param name="key">筛选条件</param>
        /// <param name="prov">省</param>
        /// <param name="city">市</param>
        /// <param name="oldStart">搜索最小岁数</param>
        /// <param name="oldEnd">搜索最大岁数</param>
        /// <param name="sex">性别</param>
        /// <param name="haveHeadPic">是否有头像</param>
        /// <param name="counts">总数[0],页数[1]</param>
        /// <returns>数据集</returns>
        public DataSet SearchFriend(int cpage, int psize, string key,string prov,string city,int oldStart,int oldEnd,int sex,bool haveHeadPic,out List<int?> counts)
        {
            Jy123.Model.MPagination mPagination = new Jy123.Model.MPagination();
            StringBuilder strFilter = new StringBuilder();
            strFilter.Append("(State=5 or State=2)");

            if (key != "") { strFilter.Append("and (Email like '"+key+"' or UserName like '%" + key + "%') "); }
            if (prov != "") { strFilter.Append("and (Province like '%" + prov + "%') "); }
            if (city != "") { strFilter.Append("and (city like '%" + city + "%') "); }
            int birthdayStart = 1850;
            int birthdayEnd = DateTime.Today.Year;
            birthdayStart = int.Parse((DateTime.Now.Year - oldEnd).ToString());
            birthdayEnd = int.Parse((DateTime.Now.Year - oldStart).ToString());
            if (birthdayStart != DateTime.Today.Year) { strFilter.Append("and (birthday>= '" + birthdayStart + "') "); }
            if (birthdayEnd != DateTime.Today.Year) { strFilter.Append("and (birthday<= '" + birthdayEnd + "') "); }

            switch(sex)
            {
                case 0:
                    strFilter.Append("and sex = '美女'");
                    break;
                case 1:
                    strFilter.Append("and sex = '帅哥'");
                    break;
            }

            if (haveHeadPic)
            {
                strFilter.Append("and portrait is not null");
            }
                

            mPagination.Tables = "V_User_SearchNameOrUser";
            mPagination.PrimaryKey = "ID";
            mPagination.Fields = "*";
            mPagination.CurrentPage = cpage;
            mPagination.Filter = strFilter.ToString();
            mPagination.PageSize = psize;
            mPagination.Sort = "ID desc";
            return Pagination.GetList(mPagination, out counts);
        }
    }
}
